package com.ztesoft.report.service.impl;

import com.alibaba.fastjson.JSON;
import com.ztesoft.report.engine.data.ReportDataSource;
import com.ztesoft.report.engine.query.QueryerFactory;
import com.ztesoft.report.entity.Datasource;
import com.ztesoft.report.exception.RException;
import com.ztesoft.report.service.DataService;
import com.ztesoft.report.service.DatasourceService;
import com.ztesoft.report.utils.AESUtil;
import com.ztesoft.report.utils.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.*;

/**
 * @author y3tu
 * @date 2018/6/21
 */
@Service
public class DataServiceImpl implements DataService {
    @Autowired
    DatasourceService datasourceService;

    @Override
    public List<Map> executeQuerySql(String dsId, String sql) {
        final ReportDataSource reportDataSource = getReportDataSource(dsId);
        return QueryerFactory.create(reportDataSource).executeQuerySql(sql);
    }

    @Override
    public List<Map> executeQuerySqlByPage(String dataSourceId, String sql, int pageSize, int pageNumber) {
        final ReportDataSource reportDataSource = getReportDataSource(dataSourceId);
        StringBuilder strSql = new StringBuilder();
        if ("1".equals(reportDataSource.getType())) {
            //mysql
            strSql.append(sql);
            strSql.append(" limit ");
            strSql.append((pageNumber-1)*pageSize);
            strSql.append(",");
            strSql.append(pageSize);
        } else if ("2".equals(reportDataSource.getType())) {
            //oracle
            strSql.append("SELECT * FROM (");
            strSql.append("  SELECT A.*, ROWNUM RN  FROM ( ");
            strSql.append(sql);
            strSql.append(" ) A WHERE ROWNUM<=");
            strSql.append(pageSize*pageNumber);
            strSql.append(" ) ");
            strSql.append(" WHERE RN > ");
            strSql.append((pageNumber-1)*pageSize);
        }
        return QueryerFactory.create(reportDataSource).executeQuerySql(strSql.toString());
    }

    @Override
    public int executeQuerySqlCount(String dataSourceId, String sql) {
        final ReportDataSource reportDataSource = getReportDataSource(dataSourceId);
        StringBuilder strSql = new StringBuilder();
        strSql.append("select count(1) as count from ( ");
        strSql.append(sql).append(" ) temp ");
        List<Map> listMap = QueryerFactory.create(reportDataSource).executeQuerySql(strSql.toString());
        if (listMap.size() > 0) {
            return Integer.valueOf(listMap.get(0).get("COUNT").toString());
        }else {
            return 0;
        }
    }

    @Override
    public int executeUpdateSql(String dsId, String sql) {
        final ReportDataSource reportDataSource = getReportDataSource(dsId);
        return QueryerFactory.create(reportDataSource).executeUpdateSql(sql);
    }

    public ReportDataSource getReportDataSource(String dsId) {
        final Datasource ds = this.datasourceService.selectById(dsId);
        Map<String, Object> options = new HashMap<>(3);
        if (StringUtils.isNotEmpty(ds.getOptions())) {
            options = JSON.parseObject(ds.getOptions());
        }
        return new ReportDataSource(
                ds.getDriverClass(),
                ds.getDatasourceType(),
                ds.getJdbcUrl(), ds.getDatasourceUser(), AESUtil.decrypt(ds.getPassword()),
                ds.getQueryerClass(),
                ds.getPoolClass(),
                options);
    }

    /**
     * 需要同时保证SQL集的回滚需求
     * 也要保证同一个数据源使用的是同一个连接（因为很多时候用到前面插入的数据，后面需要求和所以只有同一了解才能办到）
     * @param doneSqls
     * @return
     */
    @Override
    public List<Connection> executeSql(List<Map<String, String>> doneSqls) {
        List<Connection> conns = new LinkedList<>();
        //装入用过的连接，记录下对应的数据源ID，之后通过数据源ID来寻找对应的连接，
        Map<String,Connection> dsAndConn = new HashMap<>();
        for (Map<String, String> sqlAnddsId : doneSqls) {
            Connection connTrans = null;
            String dsID = sqlAnddsId.get("dataSourceId");
            if (!dsAndConn.isEmpty()) {
                //如果有对应的数据源ID能拿到连接，如果没有对应的 connTrans必然为空，这个会在executeSql内部进行判断
                connTrans = dsAndConn.get(dsID);
            }
            //封装此doneSqls ---->ReportDataCreateJob类的handleSql方法
            final ReportDataSource reportDataSource = getReportDataSource(dsID);
            Connection conn =null;
            try {
                conn = QueryerFactory.create(reportDataSource).executeSql(sqlAnddsId.get("sql"), connTrans);
            } catch (Exception e) {
                //如果SQL异常了，这个储存所有连接的集合都必须要回滚，
/*                if (conns != null && conns.size()>0) {
                    for (Connection co : conns) {
                        if (co != null) {
                            try {
                                co.rollback();
                            } catch (SQLException ex) {
                                e.printStackTrace();
                            } finally {
                                try {
                                    co.close();
                                } catch (SQLException ex) {
                                    e.printStackTrace();
                                }
                            }
                        }
                    }
                }*/
                throw new RException(e.getMessage());
            }
            dsAndConn.put(dsID,conn);
            conns.add(conn);
        }
        return conns;
    }
}
